
CREATE OR REPLACE FUNCTION MIGRA_DADOS() RETURNS INTEGER AS
$BODY$
DECLARE
  V_COD_HYDRA_ACTION  NUMERIC(20,0);
  V_COD_HYDRA_REQUEST NUMERIC(20,0);
  V_CURRENT_TIME TIMESTAMP := NOW();
  V_MESSAGE RECORD;
BEGIN
  FOR V_MESSAGE IN SELECT * FROM <OWNER>.HYDRA_MESSAGE WHERE COD_HYDRA_REQUEST IS NULL LOOP
    SELECT NEXTVAL('<OWNER>.SEQ_HYDREQ') INTO V_COD_HYDRA_REQUEST;
    INSERT INTO <OWNER>.HYDRA_REQUEST (  COD_HYDRA_REQUEST,  REQUEST_START,    REQUEST_END, STATUS)
                               VALUES (V_COD_HYDRA_REQUEST, V_CURRENT_TIME, V_CURRENT_TIME,      1);
    UPDATE <OWNER>.HYDRA_MESSAGE SET COD_HYDRA_REQUEST = V_COD_HYDRA_REQUEST WHERE COD_HYDRA_MESSAGE = V_MESSAGE.COD_HYDRA_MESSAGE;
  END LOOP;
  RETURN 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

SELECT MIGRA_DADOS();

DROP FUNCTION MIGRA_DADOS();

ALTER TABLE <OWNER>.HYDRA_MESSAGE ALTER COLUMN COD_HYDRA_REQUEST SET NOT NULL;
